SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC [dbo].[PWorkStudentSELECT]
@iK_disc INT,
@iK_vid_zanyat INT,
@n_sem INT,
@nCode NUMERIC 
AS
SELECT 
idUspevDocs[idРаботы],
KPTheme[Тема],
Options[Вариант],
NReg[Регистрационный номер],
DateReg[Дата регистрации]
FROM dbo.Student St INNER JOIN dbo.Zach Z 
       ON St.nCode=Z.nCode 
        INNER JOIN dbo.StudGrup SG 
        ON Z.Ik_zach=SG.Ik_zach 
         INNER JOIN dbo.Grup G 
          ON SG.Ik_grup=G.Ik_grup 
          INNER JOIN dbo.Uch_pl Up 
           ON G.Ik_uch_plan=Up.ik_uch_plan 
           INNER JOIN dbo.sv_disc sd 
            ON Up.ik_uch_plan=sd.ik_uch_plan 
            INNER JOIN dbo.discpln D 
             ON sd.ik_disc=D.iK_disc 
             INNER JOIN  dbo.Content_UchPl CU 
              ON sd.ik_disc_uch_plan=CU.ik_disc_uch_plan 
              INNER JOIN dbo.Semester sem 
               ON CU.n_sem=sem.n_sem 
                INNER JOIN dbo.vid_zaniat vz 
                ON CU.ik_vid_zanyat=vz.iK_vid_zanyat
               full join 
                        (select UD.ik_zach, cu.ik_upContent,UTh.idUspevDocs,NReg,DateReg,
                           UTh.KPTheme, UTh.Options   from  dbo.Content_UchPl cu
                               INNER JOIN dbo.UspevDocument UD
                              ON CU.ik_upContent=UD.Ik_upContent 
                                 INNER JOIN dbo.UspevKPTheme UTh
                               ON UD.idUspevDocs=UTh.idUspevDocs
                                 ) b
                                       ON b.ik_zach=Z.Ik_zach and CU.ik_upContent=b.ik_upContent
WHERE St.nCode=@nCode AND sem.n_sem=@n_sem AND @iK_disc=D.iK_disc AND @iK_vid_zanyat=vz.iK_vid_zanyat
      AND SG.Ik_prikazZach IS NOT NULL 
       AND SG.ik_pricOtch IS NULL ;
GO
